CREATE TABLE [dbo].[tb_BecasYPrestamos]
(
[idBeca] [int] NOT NULL IDENTITY(1, 1),
[Alumno] [int] NOT NULL,
[LineaDeByP] [int] NOT NULL,
[FechaOtorgamiento] [datetime] NULL,
[FechaVencimiento] [datetime] NULL,
[MontoMensual] [float] NULL,
[MontoTotal] [float] NULL,
[MontoRendido] [float] NULL,
[MontoDevuelto] [float] NULL,
[CuentaBancaria] [int] NULL,
[MontoLiquidado] [float] NULL,
[Expediente] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MostrarEnListado] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_tb_BecasYPrestamos_MostrarEnListado] DEFAULT ('S')
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[ControlarBajaBeneficiarios] ON [dbo].[tb_BecasYPrestamos] 
FOR DELETE 
AS
SET NOCOUNT ON
DECLARE @idbeca as int


SELECT  @idbeca=idbeca  FROM deleted

--no se pueden borrar becasoPrestamos con movimientos de liquidacion asociados a liquidaciones con Pagado en 'S'
IF (SELECT count( *) FROM tb_BecasyPrestamos b join tb_MovimientosByP m   on b.idbeca=m.idbecayprestamo join tb_liquidacionByP l on m.liquidacionByP=idliquidacionByP  WHERE b.idbeca =@idbeca and idtipodeMovimiento=1and l.Pagado in('S', 's','Si','SI') ) >=1 
 BEGIN
	ROLLBACK transaction 
	RAISERROR('No se puede eliminar este BENEFICIARIO por que tiene liquidaciones que  ya han sido pagadas.', 16,1)
 END

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[controlarDuplicados] ON [dbo].[tb_BecasYPrestamos]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @Alumno INT
DECLARE @FechaVencimiento datetime

SELECT @Alumno = I.Alumno FROM Inserted I
/*
IF EXISTS(SELECT * FROM tb_BecasYPrestamos WHERE Alumno = @Alumno AND fechavencimiento >= getdate() ) BEGIN
	RAISERROR('El ALUMNO ya tiene un beneficio en este año '  , 16,1)
	ROLLBACK
END
*/

GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[GenerarMovimientosBeneficiario] ON [dbo].[tb_BecasYPrestamos]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @MontoMensual as float ,@fecha as datetime, @IdLineaDeByP as int,@idbeca as int


SELECT @idbeca=idbeca,@MontoMensual=MontoMensual,@Fecha=FechaOtorgamiento, @IdLineaDeByP=LineaDeByP FROM inserted

IF (SELECT count( *) FROM sagenet.dbo.tb_lineasdeByP WHERE idlineadebyp=@IdLineaDeByP) <1
 BEGIN
	ROLLBACK transaction 
	RAISERROR('No existe la LINEA asociada a este BENEFICIARIO, Debe crear la linea previamente.', 16,1)
 END
ELSE
	BEGIN
		INSERT INTO tb_MovimientosBYP( [IdBecaYPrestamo], [LiquidacionByP], [Monto], [Fecha], [IdTipoDeMovimiento])
		SELECT idbeca,LBYP.idLiquidacionByP,isnull(BYP.montomensual,L.Montomensual),isnull(BYP.FechaOtorgamiento,L.FechaOtorga) ,1
		FROM TB_BECASYPRESTAMOS BYP
		JOIN TB_LINEASDEBYP L ON BYP.LINEADEBYP=L.IDLINEADEBYP
		JOIN TB_LIQUIDACIONBYP LBYP ON LBYP.IDLINEADEBYP=L.IDLINEADEBYP WHERE  BYP.idbeca=@idbeca
	END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[MantenerMontoFecha] ON [dbo].[tb_BecasYPrestamos] 
FOR INSERT 
AS
SET NOCOUNT ON

DECLARE @IdLinea INT
DECLARE @IdBeneficiario INT--idbecayprestamo
DECLARE @FechaOtorga datetime
DECLARE @FechaVenc datetime
DECLARE @MontoMensual float
DECLARE @MontoTotal float

set @FechaOtorga = null
set @FechaVenc = null
set @MontoTotal = null
set @MontoMensual = null


SELECT @IdLinea=LineaDeByP,@IdBeneficiario=idBeca , @FechaOtorga =FechaOtorgamiento ,@FechaVenc=FechaVencimiento,@MontoTotal=MontoTotal,@MontoMensual=MontoMensual FROM  Inserted 


select @FechaOtorga =isnull(@FechaOtorga, FechaOtorga), @FechaVenc=isnull(@Fechavenc, FechaVencim) , @MontoMensual =isnull(@MontoMensual,MontoMensual),@MontoTotal = isnull(@MontoTotal,MontoTotal) from tb_lineasdeByP where idlineadebyp=@idlinea



update tb_BecasYPrestamos SET FechaOtorgamiento=@FechaOtorga , FechaVencimiento=@Fechavenc,MontoMensual=@MontoMensual,MontoTotal=@MontoTotal,Montoliquidado=@MontoMensual where idBeca = @IdBeneficiario




GO
ALTER TABLE [dbo].[tb_BecasYPrestamos] ADD CONSTRAINT [PK_tb_BecasYPrestamos] PRIMARY KEY CLUSTERED  ([idBeca]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_Alumno] ON [dbo].[tb_BecasYPrestamos] ([Alumno]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_BecasYPrestamos] WITH NOCHECK ADD CONSTRAINT [FK_tb_BecasYPrestamos_tb_Alumnos] FOREIGN KEY ([Alumno]) REFERENCES [dbo].[tb_Alumnos] ([idAlumno])
GO
ALTER TABLE [dbo].[tb_BecasYPrestamos] WITH NOCHECK ADD CONSTRAINT [FK_tb_BecasYPrestamos_tb_CuentasBancarias] FOREIGN KEY ([CuentaBancaria]) REFERENCES [dbo].[tb_CuentasBancarias] ([idCuentaBancaria])
GO
ALTER TABLE [dbo].[tb_BecasYPrestamos] WITH NOCHECK ADD CONSTRAINT [FK_tb_BecasYPrestamos_tb_LineasDeBYP] FOREIGN KEY ([LineaDeByP]) REFERENCES [dbo].[tb_LineasDeBYP] ([idLineaDeBYP])
GO
